﻿CREATE PROCEDURE [dbo].[utl_CreateAuditTable]
@Force BIT=0
AS
BEGIN
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
	
	DECLARE @ErrorCode     INT
	DECLARE @RowsAffected  INT
	DECLARE @Message       VARCHAR(MAX)
	
	-- see if audit table already exists.
	IF dbo.fn_TableExists('Audit') = @True
	BEGIN
	    IF @Force = @True
	    BEGIN
	        DROP TABLE Audit
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Unable to drop table ''Audit''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 1
	        END
	    END
	    ELSE
	    BEGIN
	        SET @Message = 'Table ''Audit'' already exists.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 2
	    END
	END
	
	-- create the audit table
	CREATE TABLE Audit
	(
		ID                 BIGINT IDENTITY(1, 1) NOT NULL,
		TableName          sysname NOT NULL,
		PrimaryKeyColumns  sysname NOT NULL,
		PrimaryKeyValues   sysname NOT NULL,
		CreateDt           DATETIME NOT NULL,
		UserID             sysname NOT NULL,
		ColumnName         sysname NOT NULL,
		OldValue           VARCHAR(MAX) NULL,
		NewValue           VARCHAR(MAX) NULL
	)
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Unable to create table ''Audit''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	-- add a primary key
	ALTER TABLE Audit WITH NOCHECK ADD
	CONSTRAINT PK_Audit PRIMARY KEY CLUSTERED(ID)
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Unable to create primary key for table ''Audit''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 4
	END
	
	-- add default constraints
	ALTER TABLE Audit WITH NOCHECK ADD 
	CONSTRAINT DF_Audit_Timestamp DEFAULT(GETDATE()) FOR [CreateDt],
	CONSTRAINT DF_Audit_UserID DEFAULT(SYSTEM_USER) FOR UserID
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Unable to create default constraints for table ''Audit''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 5
	END
	
	GRANT INSERT ON Audit TO PUBLIC
	
	RETURN 0
END

